﻿using System;
using System.Data;
using System.Linq;
using System.Security;
using System.Text;

using MySql.Data.MySqlClient;

namespace Homework4
{
    public static class DB
    {
        private const int MaxVarcharLength = 21845; // 21845 is apparently the maximum length for a VARCHAR

        public const int MySqlDefaultPort = 3306;

        #region Queries

        public const string Query1 = 
@"SELECT inst_first, inst_middle, inst_last, inst_office_phone
FROM Instructor;";

        public const string Query2 = 
@"SELECT inst_first, inst_middle, inst_last, inst_office_phone
FROM Instructor;";

        public const string Query3 = 
@"SELECT DISTINCT i1.inst_first, i1.inst_middle, i1.inst_last, i1.inst_email, i2.interest
FROM Instructor i1, Title t, Interest i2
WHERE i1.inst_degree_type = ""Ph.D."" 
    AND i1.inst_degree_year > 1990
    AND t.Title = ""associate professor""
    AND i2.parent_id = i1.inst_id
    AND t.parent_id = i1.inst_id;";

        public const string Query4 = 
@"SELECT DISTINCT i1.inst_first, i1.inst_middle, i1.inst_last, t.title, i2.interest,
       c.course_id, c.course_name
FROM Interest i2, Title t, Instructor i1
LEFT JOIN Course c ON i1.inst_id = c.parent_id
WHERE i2.parent_id = i1.inst_id
    AND t.parent_id = i1.inst_id
    AND i1.inst_id IN 
    (
        SELECT si1.inst_id 
        FROM Interest si2, Title st, Instructor si1
        WHERE (si2.interest LIKE ""%Artificial Intelligence%"" OR si2.interest LIKE ""%Databases%"")
            AND st.title LIKE ""professor""
            AND si2.parent_id = si1.inst_id
            AND st.parent_id = si1.inst_id
    );";

        public const string Query5 = 
@"SELECT DISTINCT c.course_num, c.course_name, c.course_credits
FROM Course c, Prerequisite p
WHERE p.prereq = ""CSE 310""
    AND p.parent_id = c.course_id;";

        public const string Query6 = 
@"SELECT DISTINCT i1.inst_first, i1.inst_middle, i1.inst_last, 
       i1.inst_office_location, i1.inst_office_phone, h.hour
FROM Instructor i1, Hour h, Course c
WHERE i1.inst_degree_type = ""Ph.D.""
    AND i1.inst_degree_school = ""University of Maryland""
    AND c.course_name = ""Database Management""
    AND c.parent_id = i1.inst_id
    AND h.parent_id = i1.inst_id;";

        #endregion
        
        /// <summary>
        /// Local copy of the database data
        /// </summary>
        public static DbSchema Schema { get; private set; }

        /// <summary>
        /// MySQL server address
        /// </summary>
        public static string Address { get; set; }

        /// <summary>
        ///  MySQL server port
        /// </summary>
        public static int Port { get; set; }

        /// <summary>
        /// Username to connect with
        /// </summary>
        public static string Username { get; set; }

        /// <summary>
        /// Password for the user (YAY plaintext!)
        /// </summary>
        public static SecureString Password { get; set; }

        /// <summary>
        /// Name of database to use
        /// </summary>
        public static string DatabaseName { get; set; }

        static DB()
        {
            Schema = new DbSchema();
        }

        /// <summary>
        /// Loads the DB settings (except password)
        /// </summary>
        public static void LoadSettings()
        {
            Address = Properties.Settings.Default.DbAddress;
            Port = Properties.Settings.Default.DbPort;
            Username = Properties.Settings.Default.DbUsername;
            DatabaseName = Properties.Settings.Default.DbName;
        }

        /// <summary>
        /// Saves the DB settings (except password)
        /// </summary>
        public static void SaveSettings()
        {
            Properties.Settings.Default.DbAddress = Address;
            Properties.Settings.Default.DbPort = Port;
            Properties.Settings.Default.DbUsername = Username;
            Properties.Settings.Default.DbName = DatabaseName;
        }

        /// <summary>
        /// Returns a connection string for the current settings
        /// </summary>
        /// <returns></returns>
        private static string GetConnectionString()
        {
            // OldGuids, because I prefer Guids as BINARY(16) columns instead of VARCHAR(32)
            return string.Format("Server={0}; Port={1}; Username={2}; Password={3}; Database={4}; OldGuids=true;",
                                 Address,
                                 Port,
                                 Username,
                                 Password.ToInsecureString(),
                                 DatabaseName);
        }

        /// <summary>
        /// Creates a database with the given name if it doesn't exist already
        /// </summary>
        /// <param name="name">Name of the database</param>
        /// <returns>True on success</returns>
        public static bool CreateDatabase(string name)
        {
            try
            {
                DatabaseName = name;
                MySqlHelper.ExecuteNonQuery(GetConnectionString(), string.Format("CREATE DATABASE IF NOT EXISTS `{0}`;", name));
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        /// <summary>
        /// Creates the tables for DbSchema
        /// </summary>
        /// <returns>True on success</returns>
        public static bool CreateTables()
        {
            try
            {
                var createBuilder = new StringBuilder();

                foreach (DataTable table in Schema.Tables)
                {
                    createBuilder.Append(GetCreateStatementForTable(table));
                }

                // Must add FKs after all tables made in case a referenced table isn't made at the right time
                foreach (var fkConstraint in Schema.Tables.Cast<DataTable>()
                                                          .SelectMany(table => table.Constraints.Cast<Constraint>())
                                                          .OfType<ForeignKeyConstraint>()
                                                          .Where(c => !ConstraintExists(c.ConstraintName)))
                {
                    createBuilder.AppendFormat(@"ALTER TABLE `{0}`.`{1}` ADD CONSTRAINT `{2}` FOREIGN KEY `{2}` (",
                                               DatabaseName,
                                               fkConstraint.Table.TableName,
                                               fkConstraint.ConstraintName); // Guid.NewGuid().ToString("N") + fkConstraint.ConstraintName); // Guid ensures a unique name (tends to only be an issue in a larger system)

                    // Add keyed column names
                    createBuilder.AppendStrings(fkConstraint.Columns.Select(c => c.ColumnName), NameFormat, ",");

                    // Add referenced table/columns names
                    createBuilder.AppendFormat(") REFERENCES `{0}`.`{1}` (", DatabaseName,
                                               fkConstraint.RelatedTable.TableName);
                    createBuilder.AppendStrings(fkConstraint.RelatedColumns.Select(c => c.ColumnName), NameFormat,
                                                ",");
                    createBuilder.AppendLine(") ON DELETE CASCADE ON UPDATE CASCADE;");
                }

                MySqlHelper.ExecuteNonQuery(GetConnectionString(), createBuilder.ToString());
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                return false;
            }
            return true;
        }

        private const string NameFormat = "`{0}`";

        /// <summary>
        /// Gets the CREATE TABLE statement for a DataTable
        /// </summary>
        /// <param name="table">The table...yep...</param>
        /// <returns>MySQL CREATE TABLE statement for the DataTable</returns>
        private static string GetCreateStatementForTable(DataTable table)
        {
            var tableBuilder = new StringBuilder(string.Format("CREATE TABLE IF NOT EXISTS `{0}`.`{1}` (", DatabaseName, table.TableName));

            // Column definitions
            foreach (DataColumn column in table.Columns)
            {
                var unique = column.Unique && !table.PrimaryKey.Contains(column)
                                 ? " UNIQUE"
                                 : string.Empty;

                tableBuilder.AppendFormat("`{0}` {1} {2}{3}, ", 
                                          column.ColumnName, 
                                          GetTypeForColumn(column),
                                          column.AllowDBNull ? "NULL" : "NOT NULL",
                                          unique);
            }

            // Primary key constraint
            foreach (var pkConstraint in table.Constraints.OfType<UniqueConstraint>().Where(c => c.IsPrimaryKey))
            {
                tableBuilder.AppendFormat("CONSTRAINT `{0}` PRIMARY KEY `{0}` (", pkConstraint.ConstraintName); // Guid.NewGuid().ToString("N") + pkConstraint.ConstraintName); // Guid ensures a unique name (tends to only be an issue in a larger system)

                // Add keyed column names
                tableBuilder.AppendStrings(pkConstraint.Columns.Select(c => c.ColumnName), NameFormat, ",");
                tableBuilder.Append("),");
            }

            tableBuilder.Remove(tableBuilder.Length - 1, 1); // Remove trailing comma
            tableBuilder.AppendLine(");");
            return tableBuilder.ToString();
        }

        /// <summary>
        /// Gets the proper type string based on the column
        /// </summary>
        /// <param name="column">The column...duh</param>
        /// <returns>MySQL type string for the column</returns>
        private static string GetTypeForColumn(DataColumn column)
        {
            var typeString = string.Empty;
            switch (column.DataType.ToString())
            {
                case "System.String":
                    typeString = column.MaxLength == -1 || column.MaxLength > MaxVarcharLength
                                     ? "LONGTEXT" // This feels kind of bad (LONGTEXT is max length 4GB), but some content was coming back larger than the TEXT length limit
                                     : string.Format("VARCHAR({0})", column.MaxLength);
                    break;
                
                case "System.Int32":
                    typeString = "INT";
                    break;

                case "System.DateTime":
                    typeString = "DATETIME";
                    break;

                case "System.Guid":
                    typeString = "BINARY(16)";
                    break;
                    
                case "System.Boolean":
                    typeString = "BIT";
                    break;

                //TODO: Might need more types here?
            }
            return typeString;
        }
        
        /// <summary>
        /// Determines whether a constraint with the given name already exists in the database
        /// </summary>
        /// <param name="constraintName">The constraint name to check</param>
        /// <returns>True if a constraint already exists with the given name</returns>
        public static bool ConstraintExists(string constraintName)
        {
            const string query = @"SELECT COUNT(1) AS DoesExist FROM information_schema.TABLE_CONSTRAINTS
                                   WHERE CONSTRAINT_SCHEMA = '{0}'
                                     AND CONSTRAINT_NAME   = '{1}'
                                     AND CONSTRAINT_TYPE   = 'FOREIGN KEY';";

            try
            {
                return MySqlHelper.ExecuteDataRow(GetConnectionString(), string.Format(query, DatabaseName, constraintName)).Field<long>("DoesExist") == 1;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        /// <summary>
        /// Pulls down data from the database and uploads new rows to the database
        /// for all tables in the Schema
        /// </summary>
        /// <returns>True if successful</returns>
        public static bool SyncSchemaToDatabase()
        {
            try
            {
                var connString = GetConnectionString();
                using (var conn = new MySqlConnection(connString))
                {
                    foreach (DataTable table in Schema.Tables)
                    {
                        var adapter = new MySqlDataAdapter(string.Format("SELECT * FROM `{0}`.`{1}`;", DatabaseName, table.TableName), conn);
                        new MySqlCommandBuilder(adapter); // Yes, this is needed like this even if it's not being used... it's weird  :/
                        adapter.Fill(table);
                        adapter.Update(table);
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            } 
        }

        /// <summary>
        /// Runs the given SQL query against the database and returns the first
        /// DataTable result returned by it
        /// </summary>
        /// <param name="query">The query to run</param>
        /// <returns>A DataTable of the results, or null on error</returns>
        public static DataTable GetDataTableFromQuery(string query)
        {
            try
            {
                var ds = MySqlHelper.ExecuteDataset(GetConnectionString(), query);
                return (ds == null || ds.Tables.Count == 0)
                           ? null
                           : ds.Tables[0];
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return null;
            }
        }

        public static bool ClearData()
        {
            try
            {
                Schema.Instructor.Clear();
                Schema.Title.Clear();
                Schema.Hour.Clear();
                Schema.Interest.Clear();
                Schema.Course.Clear();
                Schema.Prerequisite.Clear();

                var queryBuilder = new StringBuilder("SET SQL_SAFE_UPDATES=0;");
                //queryBuilder.AppendFormat("DELETE FROM `{0}`.`{1}` WHERE Guid <> '';", DatabaseName, Schema.Webpages.TableName);
                //queryBuilder.AppendFormat("DELETE FROM `{0}`.`{1}` WHERE Guid <> '';", DatabaseName, Schema.Links.TableName);
                queryBuilder.Append("SET SQL_SAFE_UPDATES=1;");

                MySqlHelper.ExecuteNonQuery(GetConnectionString(), queryBuilder.ToString());
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }
    }
}
